package com.gable.report.common.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * Excel导出的工具类
 * @author Lesson
 *
 */
public class ChartsExportExcel {
		
		/**
		 * @Author: 杨青桦
		 * @Description:  合并列的导出方法
		 * @Date: 2018/1/10 13:48
		 * @param bodyMap  加载的数据集
		 * @param fileName   excel的名称
		 * @param response   响应客户端
		 * @param headList   表头
		 * @param footList   如果数据集为空，需要设定表头
		 * @throws IOException
		 */
	    public static void export(Map<String,List<List<String>>> bodyMap, String fileName, HttpServletResponse response, List<String> headList, List<String> footList) throws IOException {
	        HSSFWorkbook wb = new HSSFWorkbook();  
	        HSSFSheet sheet = wb.createSheet("sheet1");  
	        HSSFRow row = sheet.createRow(0);
	        HSSFCellStyle style = wb.createCellStyle();
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
			HSSFCellStyle style1 = wb.createCellStyle();
			style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中

			//表头
        	int k = 0;
			Iterator<String> it = headList.iterator();
        	while (it.hasNext()) {
        		HSSFCell cell = row.createCell(k);
	            cell.setCellValue(it.next());
	            cell.setCellStyle(style);
	            sheet.autoSizeColumn(k);  
	            k++;
			}

			//数据
			//分块合并
			int a = 1; //合并开始行
			int b = 0; //合并结束行
			int c = 1; //计算总行数
			for (Map.Entry<String,List<List<String>>> entry : bodyMap.entrySet()) {
				List<List<String>> list = entry.getValue();


				for (int i = 0; i < list.size(); i++) {
					row = sheet.createRow(c+i);
					HSSFCell cell = row.createCell(0);

					Iterator<String> it1 = list.get(i).iterator();
					int s = 1;
					while (it1.hasNext()) {
						String value = it1.next();
						row.createCell(s).setCellValue(value);
						sheet.autoSizeColumn(s);
						s++;
					}
				}

				//在sheet里增加合并单元格
				b = a + list.size();
				CellRangeAddress cra = new CellRangeAddress(a, b-1, 0, 0);
				a = b;
				sheet.addMergedRegion(cra);

				HSSFCell nowCell = sheet.getRow(c).getCell(0);
				nowCell.setCellValue(entry.getKey());
				sheet.autoSizeColumn(0);
				/*nowCell.setCellStyle(style1);*/

				c = list.size()+ c;
			}


			//表尾
			row = sheet.createRow(c);
			int t = 0;
			Iterator<String> it2 = footList.iterator();
			while (it2.hasNext()) {
				HSSFCell cell = row.createCell(t);
				cell.setCellValue(it2.next());
				sheet.autoSizeColumn(t);
				t++;
			}


	        response.reset();
	        response.setContentType("application/vnd.ms-excel;charset=utf-8");    
		    response.setHeader("Content-disposition", "attachment;filename="+new String(fileName.getBytes("gbk"), "iso8859-1")+".xls");
		    wb.write(response.getOutputStream());
		    response.flushBuffer();
		    
	    }


	/**
	 * @Author: 杨青桦
	 * @Description: 直接导出的方法
	 * @Date: 2018/1/10 13:48
	 * @param list  加载的数据集
	 * @param fileName   下载的excel的名称
	 * @param response   响应客户端
	 * @throws IOException
	 */
	public static void export(List<List<String>> list,String fileName,HttpServletResponse response) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");
		HSSFRow row;
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);


		for (int i = 0; i < list.size(); i++) {
			row = sheet.createRow(i);
			Iterator<String> its = list.get(i).iterator();
			int s = 0;
			while (its.hasNext()) {
				String value = its.next();
				row.createCell(s).setCellValue(value);
				s++;
			}
		}

		response.reset();
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		response.setHeader("Content-disposition", "attachment;filename="+new String(fileName.getBytes("gbk"), "iso8859-1")+".xls");
		wb.write(response.getOutputStream());
		response.flushBuffer();

	}

}

